Imports System.Collections
Imports System.Data.OracleClient
Imports System.IO

Public Class clsDBScriptGnrt
  Dim mStrScriptCode As String = String.Empty
  Dim mCmdData As OracleCommand
  Dim mDataAdapter As OracleDataAdapter
  Dim mDrData As OracleDataReader

  Public Function PopulateIndex(ByVal aStrTableName As String) As OracleDataReader
    Dim lObjRtn As OracleDataReader
    Dim lStrQuery As String = String.Empty

    mPrRefreshConn()

    lStrQuery = "SELECT TTL_TABL_IDXX, TTL_INDX_IDXX, TTL_INDX_NAME, TTL_INDX_TYPE, TAB_COL_NAME, TTL_COLM_IDXX, TTL_COLM_SEQC_NMBR" & _
                " FROM TRDX_TABL_INDX A, TRDX_TABLES_MSTR T , TRDX_TABLE_COLUMNS B " & _
                " WHERE (B.TAB_TABLE_ID = T.TABM_TABLE_ID AND T.TABM_TABLE_ID = A.TTL_TABL_IDXX AND B.TAB_COL_ID = A.TTL_COLM_IDXX)" & _
                " AND T.TABM_TABLE_NAME='" & aStrTableName & "' " & _
                " ORDER BY A.TTL_INDX_IDXX"

    mCmdData = New OracleCommand(lStrQuery, con)
    lObjRtn = mCmdData.ExecuteReader

    Return lObjRtn
  End Function

  Public Function PopulateFK(ByVal aIntTableId As Integer) As OracleDataReader
    Dim lObjRtn As OracleDataReader
    Dim lStrQuery As String = String.Empty

    mPrRefreshConn()

    lStrQuery = "SELECT * FROM TRDX_FRGN_KEY_DTLS T" & _
                " WHERE T.TFK_TABLE_ID=" & aIntTableId & _
                " ORDER BY T.TFK_FRGN_KEY_INDX_ID"

    mCmdData = New OracleCommand(lStrQuery, con)
    lObjRtn = mCmdData.ExecuteReader

    Return lObjRtn
  End Function

  Public Function PopulateAllIndex() As DataTable
    Dim lObjDataSet As New DataSet
    Dim lObjDataAdapter As OracleDataAdapter
    Dim lStrQuery As String = String.Empty

    mPrRefreshConn()

    lStrQuery = "SELECT TABM_TABLE_NAME, TTL_TABL_IDXX, TTL_INDX_IDXX, TTL_INDX_NAME, TTL_INDX_TYPE, TAB_COL_NAME, TTL_COLM_IDXX, TTL_COLM_SEQC_NMBR" & _
                " FROM TRDX_TABL_INDX A, TRDX_TABLES_MSTR T , TRDX_TABLE_COLUMNS B " & _
                " WHERE (B.TAB_TABLE_ID = T.TABM_TABLE_ID AND T.TABM_TABLE_ID = A.TTL_TABL_IDXX AND B.TAB_COL_ID = A.TTL_COLM_IDXX)" & _
                " ORDER BY A.TTL_INDX_IDXX"

    lObjDataAdapter = New OracleDataAdapter(lStrQuery, con)
    lObjDataAdapter.Fill(lObjDataSet)
    lObjDataAdapter.Dispose()
    lObjDataAdapter = Nothing

    Return lObjDataSet.Tables(0)
  End Function

  Public Function PopulateAllFK() As DataTable
    Dim lObjDataSet As New DataSet
    Dim lObjDataAdapter As OracleDataAdapter
    Dim lStrQuery As String = String.Empty

    mPrRefreshConn()

    lStrQuery = "SELECT * FROM TRDX_FRGN_KEY_DTLS T" & _
                " ORDER BY T.TFK_TABLE_ID,T.TFK_FRGN_KEY_ID,T.TFK_FRGN_KEY_INDX_ID"

    lObjDataAdapter = New OracleDataAdapter(lStrQuery, con)
    lObjDataAdapter.Fill(lObjDataSet)
    lObjDataAdapter.Dispose()
    lObjDataAdapter = Nothing

    Return lObjDataSet.Tables(0)
  End Function

  Public Function PopulateColmuns(ByVal aStrTableName As String) As ArrayList
    Dim lArrRtn As New ArrayList
    Dim lObjRtn As OracleDataReader
    Dim lStrQuery As String = String.Empty

    mPrRefreshConn()

    lStrQuery = "SELECT A.TAB_COL_NAME FROM TRDX_TABLE_COLUMNS A , TRDX_TABLES_MSTR B" & _
                " WHERE(A.TAB_TABLE_ID = B.TABM_TABLE_ID)" & _
                " AND B.TABM_TABLE_NAME='" & aStrTableName & "'"

    mCmdData = New OracleCommand(lStrQuery, con)
    lObjRtn = mCmdData.ExecuteReader

    If IsNothing(lObjRtn) = False Then
      While lObjRtn.Read
        lArrRtn.Add(lObjRtn.Item(0))
      End While
    End If

    Return lArrRtn
  End Function

  Public Function GetMaxIndexId() As Integer
    Dim lIntRtn As New Long
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT MAX(T.TTL_INDX_IDXX) FROM TRDX_TABL_INDX T"

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function GetMaxFKId() As Integer
    Dim lIntRtn As New Integer
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT MAX(T.TFK_FRGN_KEY_ID) FROM TRDX_FRGN_KEY_DTLS T"

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function GetPKCount(ByVal aIntTableId As Integer) As Integer
    Dim lIntRtn As New Integer
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT COUNT(*) FROM TRDX_TABL_INDX T WHERE T.TTL_INDX_TYPE='P' AND T.TTL_TABL_IDXX=" & aIntTableId

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function IsPK(ByVal aIntTableId As Integer, ByVal aIntClmId As Integer) As Boolean
    Dim lBlnRtn As New Boolean
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT COUNT(*) FROM TRDX_TABL_INDX T WHERE T.TTL_INDX_TYPE='P' AND T.TTL_TABL_IDXX=" & aIntTableId & " AND T.TTL_COLM_IDXX=" & aIntClmId

    mCmdData = New OracleCommand(lStrQuery, con)
    If mCmdData.ExecuteScalar = 1 Then
      Return True
    Else
      Return False
    End If

  End Function

  Public Function GetTableId(ByVal aStrTableName As String) As Integer
    Dim lIntRtn As New Long
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT T.TABM_TABLE_ID FROM TRDX_TABLES_MSTR T WHERE T.TABM_TABLE_NAME='" & aStrTableName & "'"

    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function GetTableShrtName(ByVal aIntTableId As Integer) As String
    Dim lStrRtn As String
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT TABM_TABLE_SHORT_NAME FROM TRDX_TABLES_MSTR T" & _
                " WHERE T.TABM_TABLE_ID=" & aIntTableId

    mCmdData = New OracleCommand(lStrQuery, con)
    lStrRtn = mCmdData.ExecuteScalar

    If IsNothing(lStrRtn) Then
      Return String.Empty
    Else
      Return lStrRtn
    End If
  End Function

  Public Function GetColmunId(ByVal aStrClmName As String, ByVal aIntTableID As Integer) As Integer
    Dim lIntRtn As Integer
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT T.TAB_COL_ID FROM TRDX_TABLE_COLUMNS T WHERE T.TAB_TABLE_ID=" & aIntTableID.ToString & " AND T.TAB_COL_NAME='" & aStrClmName & "'"
    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function GetColmunSqno(ByVal aStrClmName As String, ByVal aIntTableID As Integer) As Integer
    Dim lIntRtn As String
    Dim lStrQuery As String = String.Empty
    mPrRefreshConn()

    lStrQuery = "SELECT T.TAB_COL_SEQ_NO FROM TRDX_TABLE_COLUMNS T WHERE T.TAB_TABLE_ID=" & aIntTableID.ToString & " AND T.TAB_COL_NAME='" & aStrClmName & "'"
    mCmdData = New OracleCommand(lStrQuery, con)
    lIntRtn = mCmdData.ExecuteScalar

    If IsNothing(lIntRtn) Then
      Return 0
    Else
      Return lIntRtn
    End If
  End Function

  Public Function Update(ByVal aArrLst As ArrayList) As Boolean
    Dim lDBTransaction As OracleTransaction = Nothing
    Dim lDBCommand As OracleCommand = Nothing
    mPrRefreshConn()

    Try
      lDBTransaction = con.BeginTransaction()
      For Each lStrQuery As String In aArrLst
        lDBCommand = New OracleCommand(lStrQuery, con)
        lDBCommand.Transaction = lDBTransaction
        lDBCommand.ExecuteNonQuery()
      Next
      lDBTransaction.Commit()
      Return True
    Catch ex As Exception
      lDBTransaction.Rollback()
      Return False
    End Try
  End Function

  Public Function RemoveFK(ByVal aIntTableId As Integer) As Boolean
    Dim lStrQuery As String = String.Empty
    Dim lDBCommand As OracleCommand = Nothing

    mPrRefreshConn()
    Try
      lStrQuery = "DELETE FROM TRDX_FRGN_KEY_DTLS T WHERE T.TFK_TABLE_ID=" & aIntTableId
      lDBCommand = New OracleCommand(lStrQuery, con)
      lDBCommand.ExecuteNonQuery()
      Return True
    Catch ex As Exception
      Return False
    End Try

  End Function

End Class
